package com.gezzell.dao;

import com.gezzell.bean.Message;
import com.gezzell.db.DBAccess;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;

import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;


/**
 * 和message表相关的数据库操作
 */
public class MessageDao {
    /**
     * 根据查询条件查询消息列表----jdbc
     */
//    public List<Message> queryMessageList(String command, String description){
//        List<Message> messageList = new ArrayList<Message>();
//        try {
//            Class.forName("com.mysql.jdbc.Driver");
//            Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mybatis?characterEncoding=utf-8","root","root");
//            StringBuilder sql = new StringBuilder("SELECT ID, COMMAND, DESCRIPTION, CONTENT FROM MESSAGE WHERE 1=1");
//            List<String> paramList = new ArrayList<String>();
//            if(command!=null && !"".equals(command.trim())){
//                sql.append(" AND COMMAND=?");
//                paramList.add(command);
//            }
//            if(description!=null && !"".equals(description.trim())){
//                sql.append(" AND DESCRIPTION like '%' ? '%'");
//                paramList.add(description);
//            }
//            PreparedStatement statement = conn.prepareStatement(sql.toString());
//            for(int i = 0; i < paramList.size(); i++){
//                statement.setString(i + 1,paramList.get(i));
//            }
//            ResultSet rs = statement.executeQuery();
//
//            while(rs.next()){
//                Message message = new Message();
//                messageList.add(message);
//                message.setId(rs.getString("ID"));
//                message.setCommand(rs.getString("COMMAND"));
//                message.setDescription(rs.getString("DESCRIPTION"));
//                message.setContent(rs.getString("CONTENT"));
//            }
//        } catch (ClassNotFoundException e) {
//            e.printStackTrace();
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        return messageList;
//    }

    /**
     * 根据查询条件查询消息列表----mybatis
     * @param command
     * @param description
     * @return
     */
    public List<Message> queryMessageList(String command, String description){
        DBAccess dbAccess = new DBAccess();
        List<Message> messageList = new ArrayList<Message>();
        SqlSession sqlSession = null; // 不放到外边则需要在finally里关闭
        try {
            sqlSession = dbAccess.getSqlSession();
            Message message = new Message();
            message.setCommand(command);
            message.setDescription(description);
            // 通过sqlSession执行sql语句
            messageList = sqlSession.selectList("Message.queryMessageList", message);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if(sqlSession!=null){
                sqlSession.close();
            }
        }
        return messageList;
    }

    /**
     * 单条删除
     * @param id
     */
    public void deleteOne(int id){
        DBAccess dbAccess = new DBAccess();
        SqlSession sqlSession = null; // 不放到外边则需要在finally里关闭
        try {
            sqlSession = dbAccess.getSqlSession();
            // 通过sqlSession执行sql语句
            sqlSession.delete("Message.deleteOne", id);
            // 增删改需要手动提交事务
            sqlSession.commit();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if(sqlSession!=null){
                sqlSession.close();
            }
        }
    }

    /**
     * 多条删除
     * @param ids
     */
    public void deleteBatch(List<Integer> ids){
        DBAccess dbAccess = new DBAccess();
        SqlSession sqlSession = null; // 不放到外边则需要在finally里关闭
        try {
            sqlSession = dbAccess.getSqlSession();
            // 通过sqlSession执行sql语句
            sqlSession.delete("Message.deleteBatch", ids);
            // 增删改需要手动提交事务
            sqlSession.commit();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if(sqlSession!=null){
                sqlSession.close();
            }
        }
    }

    public static void main(String[] args){
        MessageDao messageDao = new MessageDao();
        messageDao.queryMessageList("","");
//        Logger log;
//        log.debug(message);
    }

}
